<?php
class ContractEarnedUtil{
	public static function getEarnedData($contract,$date = null){

		$processed = 0;		
		if($contract!=null){
			$startDate 			= new DateTime($contract->contractstartdate);
			$contractEndDate 	= new DateTime($contract->contractenddate);
			$inputDate			= null;
			$endDate 			= null;
			if($date!=null)
			{
				$inputDate = new DateTime($date);
				
				if($inputDate->format('Y') < $startDate->format('Y') 
					|| $inputDate->format('m') < $startDate->format('m') && $inputDate->format('Y') <= $startDate->format('Y')){
					return 0;
				}
														
				
				if($inputDate < $contractEndDate)
				{
					$endDate = new DateTime($date);

				}else
				{
					$endDate = new DateTime($contract->contractenddate);	
				}	
			}
			else
			{				
				$endDate = new DateTime($contract->contractenddate);
			}
			$termMonthsDiff = $contractEndDate->diff($startDate,true);
			$termMonths 	= $termMonthsDiff->y*12 + $termMonthsDiff->m + 1;			
			$monthPassDiff 	= $endDate->diff($startDate,false);
			$monthPass 		= $monthPassDiff->y*12 + $monthPassDiff->m + 1;	
			
			
			
			if($inputDate!=null && intval($inputDate->format('m')) == 3){				
				if(date('L',strtotime($date)) && $monthPassDiff->d == 29){				
					$monthPass++;	
				}else if($monthPassDiff->d==28){
					$monthPass++;	
				}				
			}
			
		
			$lastMonthEarned 	= 0;
			$lastMonthEarnedTax = 0;
			$lastMonthBilled 	= 0;
			$subTotalEarned 	= 0;
			$subTotalEarnedTax 	= 0;
			$totalEarned 		= 0;
		
			$lastMonthAmountCharge = null;
		
			for($currentTermMonth=0;$currentTermMonth<$monthPass;$currentTermMonth++){
				$currentDate = new DateTime($contract->contractstartdate);
				$i =$currentTermMonth + 1;
				$currentDate->add(new DateInterval("P{$i}M"));
				$monthFormat 	= $currentDate->format('Y-m');

				$currentDate->sub(new DateInterval("P1M"));							
				$month 			= $currentDate->format('m');
				$year 			= $currentDate->format('Y');	
				
				
				$exist = Yii::app()
								->db
								->createCommand('select count(*) from tbl_contract_earned where contract=:contract and month=:month and year=:year')
								->bindValues(array(
									'contract'	=>$contract->contractid,
									'month'		=>$month,
									'year'		=>$year
								))
								->queryScalar();
				if($exist == 0){
						
					$sql = "select sum(amountcharge) amountcharge, sum(invoiceamount) invoiceamount from tbl_invoice 
						where agreementno = :contractId and invoicetype = 'Invoice' and invoicedate < :month
						OR agreementno = :contractId and invoicetype = 'Credit Memo' and invoicedate < :month";
					$totalDataRow = Yii::app()->db->createCommand($sql)->bindValues(array(
						'contractId' => $contract->contractid,
						'month'		 => $monthFormat
					))->queryRow();					
					$newCurrentEarned 		= 0;	
					$newCurrentEarnedTax 	= 0;
					$billmonth 				= 0;
					if($totalDataRow!=null){

						$amountcharge 	 = $totalDataRow['amountcharge'] !=null ? $totalDataRow['amountcharge']:0;
						$invoiceamount 	 = $totalDataRow['invoiceamount']!=null ? $totalDataRow['invoiceamount']:0;
						$newBilledTax 	 = $amountcharge - $invoiceamount;
						$newBilled 		 = $invoiceamount;
						
						$earnedMonth = $currentTermMonth + 1;
						$remainingMonths = $termMonths - $currentTermMonth;
						
						if($earnedMonth>1){
							if($lastMonthAmountCharge == null){
								

								$lastMonthData = Yii::app()
												->db
												->createCommand('select 
													amountcharged,
													currentEarned,
													currentEarnedTax,
													subTotalEarned,
													subTotalEarnedTax,
													TotalEarned
													 from tbl_contract_earned where monthsEarned=:monthsEarned and contract=:contract')
												->bindValues(array(
													'monthsEarned'	=> $earnedMonth - 1,													
													'contract'		=> $contract->contractid
												))
												->queryRow();
								if($lastMonthData!=null){
									$lastMonthAmountCharge 	= $lastMonthData['amountcharged'];
									$lastMonthEarned 		= $lastMonthData['currentEarned'];
									$lastMonthEarnedTax 	= $lastMonthData['currentEarnedTax'];
									$subTotalEarned 		= $lastMonthData['subTotalEarned'];
									$subTotalEarnedTax 		= $lastMonthData['subTotalEarnedTax'];
									$totalEarned 			= $lastMonthData['TotalEarned'];
								}
							}
						}
						switch($contract->billtype){
							case 0: //yearly
								if($earnedMonth == 1)
								{						
									$newCurrentEarned 		= round($newBilled / $termMonths,2);
									$newCurrentEarnedTax 	= round($newBilledTax /$termMonths,2);
								}
								else if($earnedMonth > 1 && $earnedMonth < $termMonths)
								{								
									if($lastMonthAmountCharge != $amountcharge)
									{

										$newNotEarned 		 = $newBilled - $subTotalEarned;
										$newNotEarnedTax 	 = $newBilledTax - $subTotalEarnedTax;
										$newCurrentEarned 	 = round($newNotEarned/$remainingMonths,2);
										$newCurrentEarnedTax = round($newNotEarnedTax/$remainingMonths,2);	
								
									}
									else
									{
										$newCurrentEarned 		= $lastMonthEarned;
										$newCurrentEarnedTax 	= $lastMonthEarnedTax;

									}
								}
								else if($earnedMonth == $termMonths )
								{
									$newCurrentEarned 		= $newBilled - $subTotalEarned;
									$newCurrentEarnedTax 	= $newBilledTax - $subTotalEarnedTax;
								}
							break;
							case 1: //monthly
								// $checkDate = new DateTime($contract->contractstartdate);
								// $checkDate->add(new DateInterval())
								$invoicesCount = Yii::app()
													->db
													->createCommand('select * from tbl_invoice where invoicedate=:checkDate')
													->bindValues(array(
														'checkDate'=>$currentDate->format('Y-m-d')
													))						
													->queryScalar();
								if($invoicesCount > 0 ){
									$numberOfInvoices = Yii::app()
														->db
														->createCommand('select count(*) from tbl_invoice where agreementno =:contractid and invoicedate = :contractstartdate and invoicetype="Invoice"')
														->bindValues(array(
															'contractid'=>$contract->contractid,
															'contractstartdate'=>$contract->contractstartdate
														))
														->queryScalar();
									if($earnedMonth < $numberOfInvoices){
										$newCurrentEarned = $newBilled/ $numberOfInvoices;
										$newCurrentEarnedTax = $newBilledTax/$numberOfInvoices;
									}else{
										$newCurrentEarned = $newBilled - $subTotalEarned;
										$newCurrentEarnedTax = $newBilledTax - $subTotalEarnedTax;
									}
								}else{
									$newCurrentEarned = 0;
									$newCurrentEarnedTax = 0;
								}													
								

							break;
							case 2: //quarterly
								//check the first month
								//if there is no invoice
								//earn = 0 earn tax = 0
								//if the billmonth == 3 and no invoice in first month
								// devide by 2

								$haveEarnedValue = false;
								if($earnedMonth <=3){
									$firstMonthInvoiceExist = Yii::app()
										->db
										->createCommand('select count(*) from tbl_invoice where date_format(invoicedate,"%Y-%m") =:date and agreementno=:contract')
										->bindValues(
											array(
												'date'=>DateUtil::convertDate("Y-m",$contract->contractstartdate),
												'contract'=>$contract->contractid
											)
										)
										->queryScalar();
										
									if($earnedMonth == 1)
									{
										if($firstMonthInvoiceExist == 0)
										{
											$newCurrentEarned = 0;
											$newCurrentEarnedTax = 0;
											$haveEarnedValue = true;
										}
									}
									else
									{
										if($firstMonthInvoiceExist ==0){
								
											$newCurrentEarned = $newBilled/2;
											$newCurrentEarnedTax = $newBilledTax/2;
											$haveEarnedValue = true;
										}
									}
									$billmonth = 3;
								}								
								else if($earnedMonth <=6)
								{
									$billmonth = 6;
								}
								else if($earnedMonth <= 9)
								{
									$billmonth = 9;
								}
								else if($earnedMonth <=12)
								{
									$billmonth = 12;
								}

								if($billmonth > 3){
									//check for invoices 
									$quarterCheckDate = new DateTime($contract->contractstartdate);
									$add1 = $billmonth - 3;
									$add2 = $billmonth - 1;
									$invoicesCount = Yii::app()
														->db
														->createCommand('select count(*) from tbl_invoice where invoicedate >= :startDate and invoicedate<=:endDate')
														->bindValues(array(
															'startDate'=>$quarterCheckDate->add(new DateInterval('P'.$add1.'M'))->format('Y-m-d'),
															'endDate'=>$quarterCheckDate->add(new DateInterval('P'.$add2.'M'))->format('Y-m-d')
														))
														->queryScalar();
									if($invoicesCount == 0){
										$newCurrentEarned = 0;
										$newCurrentEarnedTax = 0;
										$haveEarnedValue = true;
									}
								}
								
								
								if($haveEarnedValue == false && $billmonth != $earnedMonth)
								{
									$newCurrentEarned 		= $newBilled / $billmonth;
									$newCurrentEarnedTax 	= $newBilledTax / $billmonth;									
								}elseif ($billmonth == $earnedMonth){
									$newCurrentEarned = $newBilled - $subTotalEarned;
									$newCurrentEarnedTax = $newBilledTax - $subTotalEarnedTax;
								}	
								
							break;

						}
						
						$lastMonthEarned 		= $newCurrentEarned;
						$lastMonthEarnedTax 	= $newCurrentEarnedTax;
						$lastMonthAmountCharge 	= $amountcharge;
									
						$subTotalEarned 	+= $newCurrentEarned;
						$subTotalEarnedTax	+= $newCurrentEarnedTax;
						$totalEarned 		+= ($newCurrentEarnedTax + $newCurrentEarned);
						$lastMonthBilled 	=  $newBilled;
						$remaining 			=  $newBilled - $subTotalEarned;
						$remainingTax		=  $newBilledTax - $subTotalEarnedTax;
					
						Yii::app()->db->createCommand()->insert('tbl_contract_earned',array(
							'contract'			=> $contract->contractid,
							'month'				=> $month,
							'year'				=> $year,
							'termMonths'		=> $termMonths,
							'monthsEarned'		=> $earnedMonth,	
							'monthsRemaining'   => $remainingMonths,				
							'amountcharged'		=> $amountcharge,
							'billed'			=> $newBilled,
							'billedTax'			=> $newBilledTax,
							'billtype'			=> $billmonth,
							'currentEarned'		=> $newCurrentEarned,
							'currentEarnedTax'	=> $newCurrentEarnedTax,
							'remaining'         => $remaining,
							'remainingTax'		=> $remainingTax,
							'subTotalEarned'	=> $subTotalEarned,
							'subTotalEarnedTax'	=> $subTotalEarnedTax,
							'TotalEarned'		=> $totalEarned,
							'county'			=> $contract->location->county,
							'state'				=> $contract->location->state,
							'contractType'		=> $contract->contractype,
							'create_date'		=> $date,
						));

						$processed++;
													
					}
				}
			}

		}
		return $processed;
		//echo $table->generate();
	}

	public static function writeAdjustmentToAllContract($date){
		$criteria 		= new CDbCriteria();
		$criteria->addCondition('(contractenddate<=:inputDate)');
		$criteria->params['inputDate'] = $date;
		$contractList 	= Contracts::model()->findAll($criteria);
		
		foreach($contractList as $contract)
		{				
			if($contract->custno != '0000235'){				
				echo $contract->contractid.' ';
				self::writeAdjustmentToSingleContract($contract,$date);					
			}
		}
	}

	public static function writeAdjustmentToSingleContract($contract,$date,&$invoiceList = null){
		$dateObject = new DateTime($date);	
	
		

			$exist = Yii::app()
						->db
						->createCommand('select count(*) from tbl_contract_earned where contract=:contractid ')
						->bindValues(array(					
							'contractid'=>$contract->contractid.'a',
					
						))
						->queryScalar();
			if( $exist == 0)
			{			
				$amountCheckCommand = Yii::app()
								->db
								->createCommand('
									select invoicedate,sum(amountcharge) amountcharged, sum(invoiceamount) invoiceamount
									from tbl_invoice 
									where 
											agreementno=:contractid 
											and (invoicetype="Invoice" or invoicetype ="Credit Memo") 
											and date_format(invoicedate,"%Y-%m") <= :checkDate'

								)
								->bindValues(array
									(													
										'contractid'=>$contract->contractid,
										'checkDate'=>$dateObject->format('Y-m')
									)
								)
								->queryRow();	;
			
				
				if( $amountCheckCommand != null && $amountCheckCommand['invoicedate']!=null && $amountCheckCommand['amountcharged']!=0)
				{
					$lastRowData = Yii::app()
								->db
								->createCommand('select * from tbl_contract_earned where contract=:contract and monthsEarned =termMonths')					
								->bindValues(array('contract'=>$contract->contractid))
								->queryRow();
					$amountcharge = abs($amountCheckCommand['amountcharged']);								
					if( $lastRowData !=null && $lastRowData['amountcharged'] != $amountcharge )
					{			
						$newBilled = $amountcharge;			
						$newCurrentEarned = $amountcharge - abs($lastRowData['amountcharged']);
						
						Yii::app()->db->createCommand()->insert('tbl_contract_earned',array(
							'contract'			=> $contract->contractid.'a',
							'month'				=> $dateObject->format('m'),
							'year'				=> $dateObject->format('Y'),
							'termMonths'		=> $lastRowData['termMonths'],
							'monthsEarned'		=> 0,					
							'amountcharged'		=> $amountcharge,
							'billed'			=> $amountcharge,
							'billedTax'			=> 0,
							'billtype'			=> 0,
							'currentEarned'		=> $newCurrentEarned,
							'currentEarnedTax'	=> 0,
							'subTotalEarned'	=> $amountcharge,
							'subTotalEarnedTax'	=> 0,
							'TotalEarned'		=> $amountcharge,
							'county'			=> $lastRowData['county'],
							'state'				=> $lastRowData['state'],
							'contractType'		=> 0,
							'create_date'		=> $date,
						));

					}
				}
			
		}																
	}

}